import tkinter as tk
from tkinter import scrolledtext
import pymssql


def open(s):
    window = tk.Tk()
    window.title("系统管理人员(删除)-" + s)
    window.geometry("500x300")
    # 设置列的权重
    window.grid_columnconfigure(0, weight=1)
    window.grid_columnconfigure(1, weight=2)
    window.grid_columnconfigure(2, weight=1)

    label1 = tk.Label(window, text="需要删除的植物名称：")
    entry1 = tk.Entry(window)
    conn = pymssql.connect(host='localhost', server='LAPTOP-QBHKFR9T', port='1433', user='Mysql',
                           password='15681743536',
                           database='植物管理系统', charset='utf8')

    if conn:
        print("连接成功！")

    def check_credentials1():
        message = entry1.get()
        cursor = conn.cursor()  # 获取游标

        try:
            sql = f"DELETE FROM 分类 WHERE  分类.名称 = '{message}'"
            cursor.execute(sql)
            if cursor.rowcount == 0:
                scrollbar.insert(tk.INSERT, "没有" + message + "的信息！")
            else:
                conn.commit()
                scrollbar.insert(tk.INSERT, "植物：" + message + "的科属种信息删除完成！")

        except Exception as e:
            scrollbar.insert(tk.INSERT, "删除失败，原因：" + str(e))
            conn.rollback()

    def check_credentials2():
        message = entry1.get()
        cursor = conn.cursor()  # 获取游标

        try:
            sql = f"DELETE FROM 分布 WHERE 分布,基本信息 WHERE 分布.植物编号= 基本信息.植物编号 AND 基本信息.植物名称= '{message}'"
            cursor.execute(sql)
            if cursor.rowcount == 0:
                scrollbar.insert(tk.INSERT, "没有" + message + "的信息！")
            else:
                conn.commit()
                scrollbar.insert(tk.INSERT, "植物：" + message + "的分布区域信息删除完成！")

        except Exception as e:
            scrollbar.insert(tk.INSERT, "删除失败，原因：" + str(e))
            conn.rollback()

    def check_credentials3():
        message = entry1.get()
        cursor = conn.cursor()  # 获取游标

        try:
            sql = f"DELETE FROM 生长环境 WHERE 生长环境,基本信息 WHERE 生长环境.植物编号= 基本信息.植物编号 AND 基本信息.植物名称= '{message}'"
            cursor.execute(sql)
            if cursor.rowcount == 0:
                scrollbar.insert(tk.INSERT, "没有" + message + "的信息！")
            else:
                conn.commit()
                scrollbar.insert(tk.INSERT, "植物：" + message + "的生长环境信息删除完成！")

        except Exception as e:
            scrollbar.insert(tk.INSERT, "删除失败，原因：" + str(e))
            conn.rollback()

    def check_credentials4():
        message = entry1.get()
        cursor = conn.cursor()  # 获取游标

        try:
            sql = f"DELETE FROM 基本信息 WHERE 基本信息 WHERE  基本信息.植物名称= '{message}'"
            cursor.execute(sql)
            if cursor.rowcount == 0:
                scrollbar.insert(tk.INSERT, "没有" + message + "的信息！")
            else:
                conn.commit()
                scrollbar.insert(tk.INSERT, "植物：" + message + "的基本信息删除完成！")

        except Exception as e:
            scrollbar.insert(tk.INSERT, "删除失败，原因：" + str(e))
            conn.rollback()
    button1 = tk.Button(window, text="删除科属种中的信息", command=lambda: check_credentials1(), width=20, height=1)
    button2 = tk.Button(window, text="删除植物分布区域信息", command=lambda: check_credentials2(), width=20,
                        height=1)  # 得到科，属，种，生长环境，分布区域
    button3 = tk.Button(window, text="删除植物生长环境信息", command=lambda: check_credentials3(), width=20,
                        height=1)  # 仅查询得到植物情况即可
    button4 = tk.Button(window, text="删除植物基本信息", command=lambda: check_credentials4(), width=20,
                        height=1)  # 仅查询得到植物情况即可
    back_button = tk.Button(window, text="返回上一级", command=window.destroy, width=20, height=1)

    label1.grid(row=0, column=0)
    entry1.grid(row=1, column=0)

    button1.grid(row=2, column=0)
    button2.grid(row=2, column=1)
    button3.grid(row=3, column=0)
    button4.grid(row=3, column=1)

    back_button.grid(row=4, column=1)
    scrollbar = scrolledtext.ScrolledText(window, wrap=tk.WORD, width=60, height=10)
    scrollbar.grid(row=5, column=0, columnspan=4, sticky="nsew")
    window.mainloop()
    pass
